﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using DAL;
using DevExpress.Web.ASPxEditors;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;

public partial class CertificateMan : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Frame.Global.GlobalFunctions.SetGridView(gridView, true);
            Frame.Global.GlobalFunctions.InitComboBoxColumn(gridView.Columns["t_sscq"], "css_cqxx", "cqmc", "cqbh");
            Frame.Global.GlobalFunctions.InitComboBoxColumn(gridView.Columns["t_dwxz"], "css_dwxz", "xzmc", "xzbh");
            Frame.Global.GlobalFunctions.InitComboBoxColumn(gridView.Columns["t_fzjg"], "css_fzjg", "jgmc", "jgbh");
        }

    }
    //导入Excel到SQL
    protected void btnImport_Click(object sender, EventArgs e)
    {
        string xlsName = fuExcel.PostedFile.FileName;
        string fileType = fuExcel.PostedFile.ContentType;
        if (fileType != "application/vnd.ms-excel")
        {
            Frame.Global.GlobalFunctions.Alert("文件格式有误");
        }
        else
        {
            //String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(xlsName) + " ;Extended Properties=Excel 8.0;";
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/" + xlsName + ";Extended Properties=Excel 8.0;";
            OleDbConnection cnnOle = new OleDbConnection(strConn);
            OleDbCommand cmdOle = new OleDbCommand("SELECT * FROM [社区服务业单位名录$]", cnnOle);
            cmdOle.CommandTimeout = 20;
            cnnOle.Open();
            OleDbDataReader drOle = cmdOle.ExecuteReader();
            string mystr = "Server=localhost;uid=sa;pwd=sa;database=CMS;";
            SqlConnection conSql = new SqlConnection(mystr);
            conSql.Open();
            SqlDataAdapter adaSql = new SqlDataAdapter("select * from css_dwxx", conSql);
            DataSet mySqlDs = new DataSet();
            adaSql.Fill(mySqlDs, "css_dwxx");
            while (drOle.Read())
            {
                DataRow oDataRow = mySqlDs.Tables["css_dwxx"].NewRow();
                oDataRow["dwmc"] = drOle.GetValue(0).ToString();
                oDataRow["sscq"] = drOle.GetValue(1).ToString();
                oDataRow["fwdz"] = drOle.GetValue(2).ToString();
                oDataRow["fzjg"] = drOle.GetValue(3).ToString();
                oDataRow["zh"] = drOle.GetValue(4).ToString();
                oDataRow["fzr"] = drOle.GetValue(5).ToString();
                oDataRow["dwxz"] = drOle.GetValue(6).ToString();
                oDataRow["fzrq"] = drOle.GetValue(7).ToString();
                mySqlDs.Tables["css_dwxx"].Rows.Add(oDataRow);
            }
            drOle.Close();
            cnnOle.Dispose();
            cnnOle.Close();
            SqlCommand cmdInsert =
                new SqlCommand("Insert into css_dwxx(dwmc,sscq,fwdz,fzjg,zh,fzr,dwxz,fzrq) values (@dwmc, @sscq,@fwdz,@fzjg,@zh,@fzr,@dwxz,@fzrq)", conSql);
            cmdInsert.Parameters.Add("@dwmc", SqlDbType.VarChar, 50, "dwmc");
            cmdInsert.Parameters.Add("@sscq", SqlDbType.VarChar, 20, "sscq");
            cmdInsert.Parameters.Add("@fwdz", SqlDbType.VarChar, 100, "fwdz");
            cmdInsert.Parameters.Add("@fzjg", SqlDbType.VarChar, 20, "fzjg");
            cmdInsert.Parameters.Add("@zh", SqlDbType.VarChar, 20, "zh");
            cmdInsert.Parameters.Add("@fzr", SqlDbType.VarChar, 10, "fzr");
            cmdInsert.Parameters.Add("@dwxz", SqlDbType.VarChar, 10, "dwxz");
            cmdInsert.Parameters.Add("@fzrq", SqlDbType.VarChar, 50, "fzrq");
            adaSql.InsertCommand = cmdInsert;
            adaSql.Update(mySqlDs, "css_dwxx");
            adaSql.Dispose();
            conSql.Dispose();
            conSql.Close();
            Frame.Global.GlobalFunctions.AlertToUrl("导入成功", "CertificateMan.aspx");
        }
    }
}